/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package uit.ieproject.dataaccess;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import uit.ieproject.entities.Person;
import uit.ieproject.utilites.DbConnection;
import uit.ieproject.utilites.Info;

/**
 *
 * @author Maivia
 */
public class PersonDAO {

    public Long addPerson(Person person) throws SQLException {
        Long id = 0L;
        Connection con = DbConnection.getConnection();
        StringBuffer sql = new StringBuffer();
        sql.append("INSERT INTO ");
        sql.append(Info.TABLE_PERSON);
        sql.append(" (name, birthday, died, nickname, hometown, father, mother,realname) ");
        sql.append(" VALUES (?,?,?,?,?,?,?,?)");
        PreparedStatement stmt = con.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);
        stmt.setString(1, person.getName());
        stmt.setString(2, person.getBirthday());
        stmt.setString(3, person.getDied());
        stmt.setString(4, person.getNickname());
        stmt.setString(5, person.getHometown());
        stmt.setString(6, person.getFather());
        stmt.setString(7, person.getMother());
        stmt.setString(8, person.getRealname());

        stmt.executeUpdate();
        ResultSet rs = stmt.getGeneratedKeys();
        if (rs.next()) {
            id = rs.getLong(1);
        }
        rs.close();
        stmt.close();
        con.close();
        return id;
    }

    public ArrayList<Person> getAllPerson() throws SQLException {
        ArrayList<Person> persons = new ArrayList<>();
        Connection con = DbConnection.getConnection();
        Statement stmt = con.createStatement();
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT * FROM ");
        sql.append(Info.TABLE_PERSON);
        ResultSet rs = stmt.executeQuery(sql.toString());
        while (rs.next()) {
            Person person = new Person();
            person.setId(rs.getLong("id"));
            person.setName(rs.getNString("name").trim());
            person.setBirthday(rs.getNString("birthday").trim());
            person.setDied(rs.getNString("died").trim());
            person.setNickname(rs.getNString("nickname").trim());
            person.setHometown(rs.getNString("hometown").trim());
            person.setFather(rs.getNString("father").trim());
            person.setMother(rs.getNString("mother").trim());
            person.setRealname(rs.getNString("realname").trim());
            persons.add(person);
        }
        rs.close();
        stmt.close();
        con.close();
        return persons;
    }

    public ArrayList<Person> getPersonById(Long id) throws SQLException {
        ArrayList<Person> persons = new ArrayList<>();
        Connection con = DbConnection.getConnection();
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT * FROM ");
        sql.append(Info.TABLE_PERSON);
        sql.append(" WHERE id=?");
        PreparedStatement stmt = con.prepareStatement(sql.toString());
        stmt.setLong(1, id);
        ResultSet rs = stmt.executeQuery();
        if (rs.next()) {
            Person person = new Person();
            person.setId(rs.getLong("id"));
            person.setName(rs.getNString("name").trim());
            person.setBirthday(rs.getNString("birthday").trim());
            person.setDied(rs.getNString("died").trim());
            person.setNickname(rs.getNString("nickname").trim());
            person.setHometown(rs.getNString("hometown").trim());
            person.setFather(rs.getNString("father").trim());
            person.setMother(rs.getNString("mother").trim());
            person.setRealname(rs.getNString("realname").trim());
            persons.add(person);
        }
        rs.close();
        stmt.close();
        con.close();
        return persons;
    }
    
    public void deletePersons(long[] ids) throws SQLException {
        Connection con = DbConnection.getConnection();
        con.setAutoCommit(false);
        Statement stmt = con.createStatement();
        for(int i=0;i<ids.length;i++){
           // String deleteActivitySQL = "DELETE FROM Activity WHERE personId="+ids[i];
            StringBuffer deleteActivitySQL = new StringBuffer();
            deleteActivitySQL.append("DELETE FROM ");
            deleteActivitySQL.append(Info.TABLE_ACTIVITY);
            deleteActivitySQL.append(" WHERE personId=");
            deleteActivitySQL.append(ids[i]);
            
            //String deletePersonSQL = "DELETE FROM Person WHERE id="+ids[i];
            StringBuffer deletePersonSQL = new StringBuffer();
            deletePersonSQL.append("DELETE FROM ");
            deletePersonSQL.append(Info.TABLE_PERSON);
            deletePersonSQL.append(" WHERE id=");
            deletePersonSQL.append(ids[i]);
            stmt.addBatch(deleteActivitySQL.toString());
            stmt.addBatch(deletePersonSQL.toString());
        }
        stmt.executeBatch();
        con.commit();
        stmt.close();
        con.close();
    }

    public int checkPersonExists(String name) throws SQLException {
        Connection con = DbConnection.getConnection();
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT COUNT(*) FROM ");
        sql.append(Info.TABLE_PERSON);
        sql.append(" WHERE name=?");
        PreparedStatement stmt = con.prepareStatement(sql.toString());
        stmt.setString(1, name);
        ResultSet rs = stmt.executeQuery();
        rs.next();
        int count = rs.getInt(1);
        rs.close();
        stmt.close();
        con.close();
        return count;
    }
    
    public ArrayList<Person> searchPersonByKeyword(String keyword) throws SQLException{
        ArrayList<Person> persons = new ArrayList<>();
        Connection con = DbConnection.getConnection();
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT * FROM ");
        sql.append(Info.TABLE_PERSON);
        sql.append(" WHERE name like ?");       
        PreparedStatement stmt = con.prepareStatement(sql.toString());
        stmt.setString(1, "%" + keyword + "%");
        ResultSet rs = stmt.executeQuery();
        while(rs.next()){
            Person person = new Person();
            person.setId(rs.getLong("id"));
            person.setName(rs.getNString("name").trim());
            person.setBirthday(rs.getNString("birthday").trim());
            person.setDied(rs.getNString("died").trim());
            person.setNickname(rs.getNString("nickname").trim());
            person.setHometown(rs.getNString("hometown").trim());
            person.setFather(rs.getNString("father").trim());
            person.setMother(rs.getNString("mother").trim());
            person.setRealname(rs.getNString("realname").trim());
            persons.add(person);
        }
        rs.close();
        stmt.close();
        con.close();
        return persons;
    }
    
    public long getIdByName(String name)throws SQLException{
        long id=0;
        Connection con = DbConnection.getConnection();
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT id FROM ");
        sql.append(Info.TABLE_PERSON);
        sql.append(" WHERE name=?");
        PreparedStatement stmt = con.prepareStatement(sql.toString());
        stmt.setString(1, name);
        ResultSet rs = stmt.executeQuery();
        if (rs.next()) {
            id = rs.getLong("id");
        }
        rs.close();
        stmt.close();
        con.close();
        return id;
    }
    
    public String getAnswerAboutPerson(String sql)throws SQLException{
        String answer = Info.NOT_MATCH_INFO;      
        Connection con = DbConnection.getConnection();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        if(rs.next()){
            answer = rs.getNString(1);
        }       
        rs.close();
        stmt.close();
        con.close();
        return  answer;
    }
}
